package rpt.yto.flex.remote.data.dao.processor;

import rpt.yto.flex.remote.data.dao.itf.IPubObject;


public class Query_dirpsn_province_center {
	

	private static Query_dirpsn_province_center querySqlFunc = new Query_dirpsn_province_center();
	
	private Query_dirpsn_province_center() {
		
	}
	
	public static final Query_dirpsn_province_center getInstance() {
		return querySqlFunc;
	}
	
	/**
	 * 
	 * @param period
	 * @return
	 * @throws Exception
	 */
	public final StringBuilder getQueryTypeCorp(String period , String unitname) throws Exception {
		
		StringBuilder builderTypeCorp = new StringBuilder();
		
		builderTypeCorp.append(" select rownum rowno , tmp.* from ( ");
		builderTypeCorp.append(" select  unitcode , unitname statistictype , sum(endcount) endcount , sum(endcount_w) endcount_w ,  ");
		builderTypeCorp.append(" sum(comcount) comcount , sum(mngcount) mngcount , sum(procount) procount , sum(procount_acc) procount_acc ,  ");
		builderTypeCorp.append(" sum(skillcount) skillcount , sum(skillcount_acc) skillcount_acc , sum(expcount) expcount , sum(expcount_acc) expcount_acc ,  ");
		builderTypeCorp.append(" sum(roundcount) roundcount , sum(roundcount_acc) roundcount_acc , sum(cscount) cscount from ( ");

		// 期末从业人员数
		builderTypeCorp.append(" select bd_corp.unitname , bd_corp.unitcode , count(1) endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeCorp.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
		builderTypeCorp.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeCorp.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeCorp.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeCorp.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeCorp.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeCorp.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeCorp.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
//		builderTypeCorp.append(" and bd_corp.unitname like '"+unitname+"' ");
		if(unitname.equals(IPubObject.NAMETYPE[0])) builderTypeCorp.append(" and bd_corp.unitdistinction = 'ORGCORP00000000A0017' ");
		if(unitname.equals(IPubObject.NAMETYPE[1])) builderTypeCorp.append(" and bd_corp.unitdistinction = 'ORGCORP00000000A0018' ");
		builderTypeCorp.append(" and bd_corp.isworkingunit = 'Y' ");

		builderTypeCorp.append(" group by bd_corp.unitname , bd_corp.unitcode ");



		builderTypeCorp.append(" union all  ");

		// 期末女性从业人员数
		builderTypeCorp.append(" select bd_corp.unitname , bd_corp.unitcode , 0 endcount , count(1) endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeCorp.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
		builderTypeCorp.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeCorp.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeCorp.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeCorp.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeCorp.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeCorp.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeCorp.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeCorp.append(" and bd_psnbasdoc.sex = '女' ");
//		builderTypeCorp.append(" and bd_corp.unitname like '"+unitname+"' ");
		if(unitname.equals(IPubObject.NAMETYPE[0])) builderTypeCorp.append(" and bd_corp.unitdistinction = 'ORGCORP00000000A0017' ");
		if(unitname.equals(IPubObject.NAMETYPE[1])) builderTypeCorp.append(" and bd_corp.unitdistinction = 'ORGCORP00000000A0018' ");
		builderTypeCorp.append(" and bd_corp.isworkingunit = 'Y' ");

		builderTypeCorp.append(" group by bd_corp.unitname , bd_corp.unitcode ");


		builderTypeCorp.append(" union all  ");

		// 主管以上管理人员数
		builderTypeCorp.append(" select bd_corp.unitname , bd_corp.unitcode , 0 endcount , 0 endcount_w , count(1) comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeCorp.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
		builderTypeCorp.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeCorp.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeCorp.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeCorp.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeCorp.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeCorp.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeCorp.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeCorp.append(" and (om_job.jobname like '%主管' or om_job.jobname like '%经理' or om_job.jobname like '%总监' or om_job.jobname like '%总裁' or om_job.jobname like '%高级专员'  or  om_job.jobname like '%专家') ");
//		builderTypeCorp.append(" and bd_corp.unitname like '"+unitname+"' ");
		if(unitname.equals(IPubObject.NAMETYPE[0])) builderTypeCorp.append(" and bd_corp.unitdistinction = 'ORGCORP00000000A0017' ");
		if(unitname.equals(IPubObject.NAMETYPE[1])) builderTypeCorp.append(" and bd_corp.unitdistinction = 'ORGCORP00000000A0018' ");
		builderTypeCorp.append(" and bd_corp.isworkingunit = 'Y' ");

		builderTypeCorp.append(" group by bd_corp.unitname , bd_corp.unitcode ");


		builderTypeCorp.append(" union all  ");

		// 经理级以上人员数
		builderTypeCorp.append(" select bd_corp.unitname , bd_corp.unitcode , 0 endcount , 0 endcount_w , 0 comcount , count(1) mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeCorp.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
		builderTypeCorp.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeCorp.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeCorp.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeCorp.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeCorp.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeCorp.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeCorp.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeCorp.append(" and (om_job.jobname like '%经理' or om_job.jobname like '%总监' or om_job.jobname like '%总裁'  or  om_job.jobname like '%专家') ");
//		builderTypeCorp.append(" and bd_corp.unitname like '"+unitname+"' ");
		if(unitname.equals(IPubObject.NAMETYPE[0])) builderTypeCorp.append(" and bd_corp.unitdistinction = 'ORGCORP00000000A0017' ");
		if(unitname.equals(IPubObject.NAMETYPE[1])) builderTypeCorp.append(" and bd_corp.unitdistinction = 'ORGCORP00000000A0018' ");
		builderTypeCorp.append(" and bd_corp.isworkingunit = 'Y' ");

		builderTypeCorp.append(" group by bd_corp.unitname , bd_corp.unitcode ");


		builderTypeCorp.append(" union all  ");

		// 专业技术人员数
		// procount

		// 取得技术职称人员数
		// procount_acc

		// 技能人员数 
		// skillcount 

		// 取得技能资格证人员数
		// skillcount_acc

		// 快递业务人员数
		builderTypeCorp.append(" select bd_corp.unitname , bd_corp.unitcode , 0 endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeCorp.append(" 0 skillcount , 0 skillcount_acc , count(1) expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
		builderTypeCorp.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeCorp.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeCorp.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeCorp.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeCorp.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeCorp.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeCorp.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeCorp.append(" and (om_job.jobname like '%业务员') ");
//		builderTypeCorp.append(" and bd_corp.unitname like '"+unitname+"' ");
		if(unitname.equals(IPubObject.NAMETYPE[0])) builderTypeCorp.append(" and bd_corp.unitdistinction = 'ORGCORP00000000A0017' ");
		if(unitname.equals(IPubObject.NAMETYPE[1])) builderTypeCorp.append(" and bd_corp.unitdistinction = 'ORGCORP00000000A0018' ");
		builderTypeCorp.append(" and bd_corp.isworkingunit = 'Y' ");

		builderTypeCorp.append(" group by bd_corp.unitname , bd_corp.unitcode ");


		builderTypeCorp.append(" union all  ");

		// 取得业务员资格证人员数
		builderTypeCorp.append(" select bd_corp.unitname , bd_corp.unitcode , 0 endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeCorp.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , count(1) expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
		builderTypeCorp.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeCorp.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeCorp.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeCorp.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeCorp.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeCorp.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeCorp.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeCorp.append(" and (om_job.jobname like '%业务员') and nvl(bd_psndoc.groupdef1 , 'N') = 'Y' ");
//		builderTypeCorp.append(" and bd_corp.unitname like '"+unitname+"' ");
		if(unitname.equals(IPubObject.NAMETYPE[0])) builderTypeCorp.append(" and bd_corp.unitdistinction = 'ORGCORP00000000A0017' ");
		if(unitname.equals(IPubObject.NAMETYPE[1])) builderTypeCorp.append(" and bd_corp.unitdistinction = 'ORGCORP00000000A0018' ");
		builderTypeCorp.append(" and bd_corp.isworkingunit = 'Y' ");

		builderTypeCorp.append(" group by bd_corp.unitname , bd_corp.unitcode ");


		builderTypeCorp.append(" union all  ");

		// 分拣操作人员数
		builderTypeCorp.append(" select bd_corp.unitname , bd_corp.unitcode , 0 endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeCorp.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , count(1) roundcount , 0 roundcount_acc , 0 cscount ");
		builderTypeCorp.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeCorp.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeCorp.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeCorp.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeCorp.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeCorp.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeCorp.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeCorp.append(" and (om_job.jobname like '操作%') ");
//		builderTypeCorp.append(" and bd_corp.unitname like '"+unitname+"' ");
		if(unitname.equals(IPubObject.NAMETYPE[0])) builderTypeCorp.append(" and bd_corp.unitdistinction = 'ORGCORP00000000A0017' ");
		if(unitname.equals(IPubObject.NAMETYPE[1])) builderTypeCorp.append(" and bd_corp.unitdistinction = 'ORGCORP00000000A0018' ");
		builderTypeCorp.append(" and bd_corp.isworkingunit = 'Y' ");

		builderTypeCorp.append(" group by bd_corp.unitname , bd_corp.unitcode ");


		builderTypeCorp.append(" union all  ");

		// 取得操作员资格证人员
		builderTypeCorp.append(" select bd_corp.unitname , bd_corp.unitcode , 0 endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeCorp.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , count(1) roundcount_acc , 0 cscount ");
		builderTypeCorp.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeCorp.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeCorp.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeCorp.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeCorp.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeCorp.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeCorp.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeCorp.append(" and (om_job.jobname like '操作%') and nvl(bd_psndoc.groupdef1 , 'N') = 'Y'  ");
//		builderTypeCorp.append(" and bd_corp.unitname like '"+unitname+"' ");
		if(unitname.equals(IPubObject.NAMETYPE[0])) builderTypeCorp.append(" and bd_corp.unitdistinction = 'ORGCORP00000000A0017' ");
		if(unitname.equals(IPubObject.NAMETYPE[1])) builderTypeCorp.append(" and bd_corp.unitdistinction = 'ORGCORP00000000A0018' ");
		builderTypeCorp.append(" and bd_corp.isworkingunit = 'Y' ");

		builderTypeCorp.append(" group by bd_corp.unitname , bd_corp.unitcode ");


		builderTypeCorp.append(" union all  ");

		// 客服人员数
		builderTypeCorp.append(" select bd_corp.unitname , bd_corp.unitcode , 0 endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeCorp.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , count(1) cscount ");
		builderTypeCorp.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeCorp.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeCorp.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeCorp.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeCorp.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeCorp.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeCorp.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
//		builderTypeCorp.append(" and bd_corp.unitname like '"+unitname+"' ");
		if(unitname.equals(IPubObject.NAMETYPE[0])) builderTypeCorp.append(" and bd_corp.unitdistinction = 'ORGCORP00000000A0017' ");
		if(unitname.equals(IPubObject.NAMETYPE[1])) builderTypeCorp.append(" and bd_corp.unitdistinction = 'ORGCORP00000000A0018' ");
		builderTypeCorp.append(" and bd_corp.isworkingunit = 'Y' ");
		builderTypeCorp.append(" and (om_job.jobname like '%客%')  ");

		builderTypeCorp.append(" group by bd_corp.unitname , bd_corp.unitcode ");


		builderTypeCorp.append(" ) group by unitcode , unitname  ");
		builderTypeCorp.append(" order by unitcode asc) tmp ");
		
		return builderTypeCorp;
	}
	
	
	public final String getQuerySQLByType0(String period) throws Exception {
		return Query_dirpsn_province_center.getInstance().getQueryTypeCorp(period , IPubObject.NAMETYPE[0]).toString();
	}

	public final String getQuerySQLByType1(String period) throws Exception {
		return Query_dirpsn_province_center.getInstance().getQueryTypeCorp(period , IPubObject.NAMETYPE[1]).toString();
	}

}
